In [1]:
import IPython.core.display as di

# This line will hide code by default when the notebook is exported as HTML
di.display_html('<script>jQuery(function() {if (jQuery("body.notebook_app").length == 0) { jQuery(".input_area").toggle(); jQuery(".prompt").toggle();}});</script>', raw=True)

# This line will add a button to toggle visibility of code blocks, for use with the HTML export version
#di.display_html('''<button onclick="jQuery('.input_area').toggle(); jQuery('.prompt').toggle();">Toggle code</button>''', raw=True)
In [2]:
import pandas as pd
import numpy as np
import plotly
import cufflinks as cf
from plotly.graph_objs import Scatter, Layout
import summary_functions as scf

plotly.offline.init_notebook_mode()
cf.set_config_file(offline=True, world_readable=True, theme='ggplot')
In [3]:
input_file = 'network_summary_detailed.xlsx'
In [4]:
screenline_dict = {'Primary': {
4: 'Tacoma - East of CBD',
14: 'Auburn',
15: 'Auburn',
22: 'Tukwila',
23: 'Renton',
29: 'Seattle - South of CBD',
30: 'Bellevue/Redmond',
32: 'TransLake',
35: 'Ship Canal',
37: 'Kirkland/Redmond',
41: 'Seattle - North',
43: 'Lynnwood/Bothell',
44: 'Bothell',
46: 'Mill Creek'},
'Secondary': {
2: 'Parkland',
3: 'Puyallup',
7: 'Tacoma Narrows',
18: 'Maple Valley',
19: 'SeaTac',
20: 'Kent',
54: 'Gig Harbor',
57: 'Kitsap - North',
58: 'Agate Pass',
60: 'Cross-Sound',
66: 'Preston, Issaquah',
71: 'Woodinville'}}

observed_screenline_volumes = {'Tacoma - East of CBD': 271777,
'Auburn': 534811,
'Tukwila': 239527,
'Renton': 81758,
'Seattle - South of CBD': 490806,
'Bellevue/Redmond': 354612,
'TransLake': 250220,
'Ship Canal': 521155,
'Kirkland/Redmond': 381331,
'Seattle - North': 327021,
'Lynnwood/Bothell': 231368,
'Bothell': 255590,
'Mill Creek': 350492,
'Parkland': 285859,
'Puyallup': 118726,
'Tacoma Narrows': 79000,
'Maple Valley': 61921,
'SeaTac': 71364,
'Kent': 504607,
'Gig Harbor': 58503,
'Kitsap - North': 97177,
'Agate Pass': 21000,
'Cross-Sound': 17466,
'Preston, Issaquah': 93227,
'Woodinville': 98331}
In [5]:
# primary screenline plot
screenline_df = pd.read_excel(input_file,sheetname='Screenline Volumes')
screenline_type = 'Primary'
screenline_df['Screenline Name'] = screenline_df['Screenline'].map(screenline_dict[screenline_type])
screenline_df = screenline_df.groupby('Screenline Name').sum()
screenline_df.loc['Auburn', 'Screenline'] = '14/15'
screenline_df = screenline_df.dropna()
screenline_df = screenline_df.reset_index()
screenline_df['Observed Volume'] = screenline_df['Screenline Name'].map(observed_screenline_volumes)
screenline_df = screenline_df.set_index('Screenline Name')
screenline_df['Modeled Volume'] = screenline_df['Volumes']
del screenline_df['Volumes']
screenline_df = screenline_df[['Screenline', 'Modeled Volume', 'Observed Volume']]
screenline_df['Est/Obs'] = (screenline_df['Modeled Volume']/screenline_df['Observed Volume']).round(2)
screenline_df_primary = scf.get_differences(screenline_df, 'Modeled Volume', 'Observed Volume', -2)
screenline_df_primary
summary_functions.py:49: SettingWithCopyWarning:


A value is trying to be set on a copy of a slice from a DataFrame

See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy

summary_functions.py:50: SettingWithCopyWarning:


A value is trying to be set on a copy of a slice from a DataFrame

See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy

summary_functions.py:51: SettingWithCopyWarning:


A value is trying to be set on a copy of a slice from a DataFrame

See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy

Out[5]:
Screenline Modeled Volume Observed Volume Est/Obs Difference % Difference
Screenline Name
Auburn 14/15 540800.0 534800 1.01 6000.0 1.11
Bellevue/Redmond 30 349300.0 354600 0.99 -5300.0 -1.50
Bothell 44 281800.0 255600 1.10 26300.0 10.27
Kirkland/Redmond 37 421200.0 381300 1.10 39800.0 10.45
Lynnwood/Bothell 43 252800.0 231400 1.09 21500.0 9.28
Mill Creek 46 370500.0 350500 1.06 20000.0 5.70
Renton 23 58700.0 81800 0.72 -23100.0 -28.25
Seattle - North 41 350300.0 327000 1.07 23200.0 7.11
Seattle - South of CBD 29 482200.0 490800 0.98 -8600.0 -1.74
Ship Canal 35 556500.0 521200 1.07 35400.0 6.79
Tacoma - East of CBD 4 316600.0 271800 1.17 44900.0 16.51
TransLake 32 266700.0 250200 1.07 16500.0 6.59
Tukwila 22 227100.0 239500 0.95 -12500.0 -5.20
In [6]:
screenline_df_primary[['Modeled Volume','Observed Volume']].iplot(kind='bar',title='Model vs. Observed counts by Primary Screenline',
                                                                  xTitle='Primary Screeline',yTitle='Counts')
In [7]:
# secondary screenline plot
screenline_df = pd.read_excel(input_file,sheetname='Screenline Volumes')
screenline_type = 'Secondary'
screenline_df['Screenline Name'] = screenline_df['Screenline'].map(screenline_dict[screenline_type])
screenline_df = screenline_df.groupby('Screenline Name').sum()
screenline_df.loc['Auburn', 'Screenline'] = '14/15'
screenline_df = screenline_df.dropna()
screenline_df = screenline_df.reset_index()
screenline_df['Observed Volume'] = screenline_df['Screenline Name'].map(observed_screenline_volumes)
screenline_df = screenline_df.set_index('Screenline Name')
screenline_df['Modeled Volume'] = screenline_df['Volumes']
del screenline_df['Volumes']
screenline_df = screenline_df[['Screenline', 'Modeled Volume', 'Observed Volume']]
screenline_df['Est/Obs'] = (screenline_df['Modeled Volume']/screenline_df['Observed Volume']).round(2)
screenline_df_secondary = scf.get_differences(screenline_df, 'Modeled Volume', 'Observed Volume', -2)
screenline_df_secondary
Out[7]:
Screenline Modeled Volume Observed Volume Est/Obs Difference % Difference
Screenline Name
Agate Pass 58 22100.0 21000 1.05 1100.0 5.46
Cross-Sound 60 26100.0 17500 1.50 8600.0 49.51
Gig Harbor 54 79600.0 58500 1.36 21100.0 36.02
Kent 20 518900.0 504600 1.03 14300.0 2.84
Kitsap - North 57 78200.0 97200 0.80 -19000.0 -19.51
Maple Valley 18 58300.0 61900 0.94 -3600.0 -5.82
Parkland 2 249100.0 285900 0.87 -36800.0 -12.87
Preston, Issaquah 66 87000.0 93200 0.93 -6200.0 -6.63
Puyallup 3 120400.0 118700 1.01 1700.0 1.41
SeaTac 19 76600.0 71400 1.07 5200.0 7.35
Tacoma Narrows 7 86000.0 79000 1.09 7000.0 8.92
Woodinville 71 113800.0 98300 1.16 15500.0 15.72
In [8]:
screenline_df_secondary[['Modeled Volume','Observed Volume']].iplot(kind='bar',title='Model vs. Observed counts by Secondary Screenline',
                                                                  xTitle='Secondary Screeline',yTitle='Counts')
In [8]:
 
In [8]: